package com.wnzt.baszh.util.common;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;


public class PageHelperService {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@SuppressWarnings("unchecked")
	public PageHelper queryMssqlSplitPageByProc(final String columns, final String tablenames, final String whereCondition, final String orderColumns, 
			final int isOrderByAsc, final String keyword, final int currentPage, final int pageSize) {
		final PageHelper page = new PageHelper();
		this.jdbcTemplate.execute(new CallableStatementCreator() {
			
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				String callProc = "{call MsSql2KPagination(?,?,?,?,?,?,?,?,?,?)}";
				CallableStatement cs = conn.prepareCall(callProc);
				cs.setString(1, columns);
				cs.setString(2, tablenames);
				cs.setString(3, whereCondition);
				cs.setString(4, orderColumns);//需要排序的字段
				cs.setInt(5, isOrderByAsc); //0降序  1升序
				cs.setString(6, keyword);
				cs.setInt(7, currentPage);
				cs.setInt(8, pageSize);
				cs.registerOutParameter(9, Types.INTEGER);
				cs.registerOutParameter(10, Types.INTEGER);
				return cs;
			}
		}, new CallableStatementCallback(){

			@Override
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				List<Map<String,Object>> resultmaps = new ArrayList<Map<String,Object>>();
				cs.execute();
				ResultSet rs = cs.getResultSet();
				ResultSetMetaData meta = rs.getMetaData();
				List<String> cols = new ArrayList<String>();
				List<Integer> colstype = new ArrayList<Integer>();
				for(int i=1; i<=meta.getColumnCount(); i++){
					cols.add(meta.getColumnName(i));
					colstype.add(meta.getColumnType(i));
				}
				while(rs.next()){
					Map<String,Object> map = new HashMap<String, Object>();
					for(int k=1; k<=cols.size(); k++){
						String col = cols.get(k-1);
						int coltype = colstype.get(k-1);
						//System.out.println(col+":"+coltype+":"+rs.getObject(col));
						switch (coltype) {
						case SqlTypes.BIT:
						case SqlTypes.TINYINT:
						case SqlTypes.SMALLINT:
						case SqlTypes.INTEGER:
						case SqlTypes.BIGINT:
							map.put(col, rs.getInt(k));
							break;
						case SqlTypes.FLOAT:
						case SqlTypes.REAL:
						case SqlTypes.DOUBLE:
						case SqlTypes.NUMERIC:
						case SqlTypes.DECIMAL:
							map.put(col, rs.getFloat(k));
							break;
						case SqlTypes.CHAR:
						case SqlTypes.VARCHAR:
						case SqlTypes.LONGVARCHAR:
						case SqlTypes.NCHAR:
						case SqlTypes.NVARCHAR:
						case SqlTypes.LONGNVARCHAR:
							map.put(col, rs.getString(k));
							break;
						case SqlTypes.DATE:
							map.put(col, rs.getDate(k));
							break;
						case SqlTypes.TIME:
							map.put(col, rs.getTime(k));
							break;
						case SqlTypes.TIMESTAMP:
							map.put(col, rs.getTimestamp(k));
							break;
						default:
							break;
						}
					}
					resultmaps.add(map);
				}
				page.setRows(resultmaps);
				if(!cs.getMoreResults()){
					Integer total = cs.getInt(10);
					page.setTotal(total);
				}
				rs.close();
				return page;
			}
			
		});
		return page;
	}
}
